﻿using DapperBaseDal.Expressions;
using Dapper;
using DapperExtensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;

namespace DapperBaseDal
{
    public abstract class BaseRepository<T> : IDisposable where T : class
    {
        #region 基础参数
        public IDbConnection Connection { private set; get; }
        public DatabaseType DbType { private set; get; }
        public DapperAsyncImplementor DapperImp { private set; get; }
        public IDbTransaction Transaction { private set; get; }
        public string ConnectionString { private set; get; }
        /// <summary>
        /// 命令超时时间
        /// </summary>
        public int? CommandTimeout { get; set; }
        /// <summary>
        /// 是否要缓冲
        /// </summary>
        public bool Buffered { get; set; } = false;
        /// <summary>
        /// 是否有事务
        /// </summary>
        public bool HasActiveTransaction
        {
            get { return this.Transaction != null; }
        }
        #endregion

        #region 构造函数
        /// <summary>
        /// 要配合UseDatabase，UseTransaction使用
        /// </summary>
        /// <param name="dbType"></param>
        public BaseRepository(DatabaseType dbType = DatabaseType.MySql)
        {
            this.DbType = dbType;
            this.DapperImp = BuildDapperImplementor();
        }
        public BaseRepository(string connectionString, DatabaseType dbType = DatabaseType.MySql)
        {
            this.DbType = dbType;
            this.DapperImp = BuildDapperImplementor();
            InitConnection(connectionString);
        }
        #endregion

        #region 初始配置相关
        /// <summary>
        /// 初始化Connection对象
        /// </summary>
        /// <param name="connectionString"></param>
        public void InitConnection(string connectionString)
        {
            this.Dispose();
            this.Connection = BuildConnection(connectionString);
            this.ConnectionString = this.Connection.ConnectionString;
        }

        /// <summary>
        /// 使用外部Connection对象
        /// </summary>
        /// <param name="connection"></param>
        public void UseConnection(IDbConnection connection)
        {
            this.Dispose();
            this.Connection = connection;
            this.ConnectionString = connection.ConnectionString;
        }
        /// <summary>
        /// 用于构建connection对象的方法
        /// 不同类型的数据库，生成不同的connection对象
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        public abstract IDbConnection BuildConnection(string connectionString);
        /// <summary>
        /// 生成Dapper工具对象， 该方法可重写
        /// </summary>
        /// <returns></returns>
        public virtual DapperAsyncImplementor BuildDapperImplementor()
        {
            return BuildFactory.CreateDapperImplementor(this.DbType);
        }

        /// <summary>
        /// 关闭事务，关闭连接并释放连接
        /// </summary>
        public void Dispose()
        {
            CloseTransaction();
            CloseConnection();
        }

        /// <summary>
        /// 关闭事务，关闭连接
        /// </summary>
        public void Close()
        {
            CloseTransaction();
            CloseConnection(false);
        }

        /// <summary>
        /// 关闭事务并释放
        /// 当isRollback=true时，在连接未关闭时会先回滚事务
        /// </summary>
        /// <param name="isRollback">是否回滚事务，默认是</param>
        public void CloseTransaction(bool isRollback = true)
        {
            if (HasActiveTransaction)
            {
                if (isRollback)
                {
                    if (Transaction.Connection != null && Transaction.Connection.State != ConnectionState.Closed)
                    {
                        Transaction.Rollback();
                    }
                }
                Transaction.Dispose();
                Transaction = null;
            }
        }
        /// <summary>
        /// 关闭连接
        /// </summary>
        /// <param name="disposed">是否释放连接</param>
        public void CloseConnection(bool disposed = true)
        {
            if (this.Connection != null)
            {
                if (this.Connection.State != ConnectionState.Closed)
                {
                    this.Connection.Close();
                }
                if (disposed)
                {
                    this.Connection.Dispose();
                    this.Connection = null;//这个很重要
                }
            }
        }
        /// <summary>
        /// 打开连接
        /// </summary>
        public void OpenConnection()
        {
            if (this.Connection != null)
            {
                if (this.Connection.State != ConnectionState.Open)
                {
                    this.Connection.Open();
                }
            }
            else
            {
                this.Connection = BuildConnection(this.ConnectionString);
                if (this.Connection.State != ConnectionState.Open)
                {
                    this.Connection.Open();
                }
            }
        }
        #endregion

        #region 执行委托
        /// <summary>
        /// 执行委托
        /// 使用事务时，不能关闭连接
        /// </summary>
        /// <typeparam name="TAny"></typeparam>
        /// <param name="execSqlFunc"></param>
        /// <returns></returns>
        public TAny RunInConnection<TAny>(Func<IDbConnection, TAny> execSqlFunc)
        {
            try
            {
                OpenConnection();
                var result = execSqlFunc(this.Connection);
                return result;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (!HasActiveTransaction)
                {
                    this.Close();
                }
            }
        }
        /// <summary>
        /// 执行委托
        /// 使用事务时，不能关闭连接
        /// </summary>
        /// <param name="execSqlFunc"></param>
        public void RunInConnection(Action<IDbConnection> execSqlFunc)
        {
            try
            {
                OpenConnection();
                execSqlFunc(this.Connection);
                return;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (!HasActiveTransaction)
                {
                    this.Close();
                }
            }
        }

        #endregion

        #region 事务相关
        public void BeginTransaction(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            OpenConnection();
            this.Transaction = this.Connection.BeginTransaction(isolationLevel);
        }
        public void Commit()
        {
            this.Transaction.Commit();
            this.CloseTransaction(false);
            this.CloseConnection();
        }
        public void Rollback()
        {
            this.Transaction.Rollback();
            this.CloseTransaction(false);
            this.CloseConnection();
        }
        /// <summary>
        /// 使用外部事务，此方法会将事务的Connection连接对象置为同一个Connection连接对象，因为事务须在同一个Connection连接对象下才能使用
        /// 多表联合事务时，UseTransaction可配合在RunInTransaction方法内使用
        /// </summary>
        /// <param name="dbTrans"></param>
        public void UseTransaction(IDbTransaction dbTrans)
        {
            this.Dispose();
            this.Transaction = dbTrans;
            this.Connection = dbTrans.Connection;
            this.ConnectionString = this.Connection.ConnectionString;
        }
        /// <summary>
        /// 执行事务委托
        /// 多表联合事务时，UseTransaction可配合在RunInTransaction方法内使用
        /// </summary>
        /// <typeparam name="TAny"></typeparam>
        /// <param name="execTranFunc"></param>
        /// <param name="isolationLevel"></param>
        /// <returns></returns>
        public TAny RunInTransaction<TAny>(Func<IDbTransaction, TAny> execTranFunc, IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            try
            {
                if (!this.HasActiveTransaction)
                {
                    BeginTransaction(isolationLevel);
                }
                TAny result = execTranFunc(Transaction);
                this.Commit();
                return result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
        }
        /// <summary>
        /// 执行事务委托
        /// 多表联合事务时，UseTransaction可配合在RunInTransaction方法内使用
        /// </summary>
        /// <param name="execTranFunc"></param>
        /// <param name="isolationLevel"></param>
        /// <returns></returns>
        public void RunInTransaction(Action<IDbTransaction> execTranFunc, IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            try
            {
                if (!this.HasActiveTransaction)
                {
                    BeginTransaction(isolationLevel);
                }
                execTranFunc(Transaction);
                this.Commit();
                return;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
        }

        #endregion

        #region 查询数量Count
        public int Count(object predicate = null)
        {
            return RunInConnection(conn =>
            {
                return DapperImp.Count<T>(conn, predicate, Transaction, CommandTimeout);
            });
        }
        public int Count(Expression<Func<T, bool>> predicate)
        {
            return RunInConnection(conn =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                return DapperImp.Count<T>(conn, predicateGp, Transaction, CommandTimeout);
            });
        }
        #endregion

        #region 查询单个实体
        public T Get(dynamic id)
        {
            return RunInConnection(conn =>
            {
                return DapperImp.Get<T>(conn, id, Transaction, CommandTimeout);
            });
        }
        public T GetFirst(object predicate = null, object sort = null)
        {
            return RunInConnection(conn =>
            {
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                return DapperImp.GetSet<T>(conn, predicate, sorts, 0, 1, Transaction, CommandTimeout, Buffered).FirstOrDefault();
            });
        }
        public T GetFirst(Expression<Func<T, bool>> predicate)
        {
            return RunInConnection(conn =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = SortingExtensions.GetDefaultSort<T>();
                return DapperImp.GetSet<T>(conn, predicateGp, sorts, 0, 1, Transaction, CommandTimeout, Buffered).FirstOrDefault();
            });
        }
        public T GetFirst(Expression<Func<T, bool>> predicate, object sort)
        {
            return RunInConnection(conn =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                return DapperImp.GetSet<T>(conn, predicateGp, sorts, 0, 1, Transaction, CommandTimeout, Buffered).FirstOrDefault();
            });
        }
        public T GetFirst(Expression<Func<T, bool>> predicate, IList<ISort> sort)
        {
            return RunInConnection(conn =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sort ?? SortingExtensions.GetDefaultSort<T>();
                return DapperImp.GetSet<T>(conn, predicateGp, sorts, 0, 1, Transaction, CommandTimeout, Buffered).FirstOrDefault();
            });
        }
        public T GetFirst(Expression<Func<T, bool>> predicate, SortDirection ascending, params Expression<Func<T, object>>[] sortingExpression)
        {
            return RunInConnection(conn =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sortingExpression.ToSortable(ascending) ?? SortingExtensions.GetDefaultSort<T>();
                return DapperImp.GetSet<T>(conn, predicateGp, sorts, 0, 1, Transaction, CommandTimeout, Buffered).FirstOrDefault();
            });
        }
        #endregion

        #region 查询列表
        public IEnumerable<T> GetList(object predicate = null, object sort = null)
        {
            return RunInConnection(conn =>
            {
                var sorts = sort.ToSortable();
                return DapperImp.GetList<T>(conn, predicate, sorts, Transaction, CommandTimeout, Buffered);
            });
        }
        /// <summary>
        /// 查询列表
        ///   orm 拼接条件 查询
        ///   IList《IPredicate》 predList = new List《IPredicate》();
        ///   predList.Add(Predicates.Field《DemoEntity》(p => p.Name, Operator.Like, "不知道%"));
        ///   predList.Add(Predicates.Field《DemoEntity》(p => p.ID, Operator.Eq, 1));
        ///   IPredicateGroup predGroup = Predicates.Group(GroupOperator.And, predList.ToArray());        
        /// </summary>
        /// <param name="predicate"></param>
        /// <param name="sort"></param>
        /// <returns></returns>
        public IEnumerable<T> GetList(object predicate, IList<ISort> sort)
        {
            return RunInConnection(conn =>
            {
                return DapperImp.GetList<T>(conn, predicate, sort, Transaction, CommandTimeout, Buffered);
            });
        }
        public IEnumerable<T> GetList(Expression<Func<T, bool>> predicate, IList<ISort> sort = null)
        {
            return RunInConnection(conn =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                return DapperImp.GetList<T>(conn, predicateGp, sort, Transaction, CommandTimeout, Buffered);
            });
        }
        public IEnumerable<T> GetList(Expression<Func<T, bool>> predicate, SortDirection ascending, params Expression<Func<T, object>>[] sortingExpression)
        {
            return RunInConnection(conn =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sort = sortingExpression.ToSortable(ascending);
                return DapperImp.GetList<T>(conn, predicateGp, sort, Transaction, CommandTimeout, Buffered);
            });
        }
        #endregion

        #region 查询区间列表

        /// <summary>
        /// 根据查询条件和排序条件获取实体区间列表
        /// （查询使用谓词或匿名对象，排序使用Sort或匿名对象）
        /// </summary>
        /// <param name="firstResult">起始行数</param>
        /// <param name="maxResults">最大条数</param>        
        /// <param name="predicate">查询条件</param>
        /// <param name="sort">排序条件</param>
        /// <returns>实体区间列表</returns>
        public IEnumerable<T> GetSet(int firstResult, int maxResults, object predicate = null, object sort = null)
        {
            return RunInConnection(conn =>
            {
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                return DapperImp.GetSet<T>(conn, predicate, sorts, firstResult, maxResults, Transaction, CommandTimeout, Buffered);
            });
        }
        public IEnumerable<T> GetSet(int firstResult, int maxResults, object predicate, IList<ISort> sort)
        {
            return RunInConnection(conn =>
            {
                var sorts = sort ?? SortingExtensions.GetDefaultSort<T>();
                return DapperImp.GetSet<T>(conn, predicate, sorts, firstResult, maxResults, Transaction, CommandTimeout, Buffered);
            });
        }
        public IEnumerable<T> GetSet(int firstResult, int maxResults, Expression<Func<T, bool>> predicate, object sort)
        {
            return RunInConnection(conn =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                return DapperImp.GetSet<T>(conn, predicateGp, sorts, firstResult, maxResults, Transaction, CommandTimeout, Buffered);
            });
        }
        public IEnumerable<T> GetSet(int firstResult, int maxResults, Expression<Func<T, bool>> predicate, IList<ISort> sort)
        {
            return RunInConnection(conn =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sort ?? SortingExtensions.GetDefaultSort<T>();
                return DapperImp.GetSet<T>(conn, predicateGp, sorts, firstResult, maxResults, Transaction, CommandTimeout, Buffered);
            });
        }
        public IEnumerable<T> GetSet(int firstResult, int maxResults, Expression<Func<T, bool>> predicate, SortDirection ascending, params Expression<Func<T, object>>[] sortingExpression)
        {
            return RunInConnection(conn =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sortingExpression.ToSortable(ascending) ?? SortingExtensions.GetDefaultSort<T>();
                return DapperImp.GetSet<T>(conn, predicateGp, sorts, firstResult, maxResults, Transaction, CommandTimeout, Buffered);
            });
        }

        #endregion

        #region 查询分页列表

        public IEnumerable<T> GetPage(int pageIndex, int pageSize, out int totalCount, object predicate, object sort)
        {
            totalCount = 0;
            var results = RunInConnection(conn =>
            {
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                int count = DapperImp.Count<T>(conn, predicate, Transaction, CommandTimeout);
                var entities = DapperImp.GetPage<T>(conn, predicate, sorts, pageIndex - 1, pageSize, Transaction, CommandTimeout, Buffered);
                return Tuple.Create(entities, count);
            });
            totalCount = results.Item2;
            return results.Item1;
        }
        public IEnumerable<T> GetPage(int pageIndex, int pageSize, out int totalCount, object predicate, IList<ISort> sort)
        {
            totalCount = 0;
            var results = RunInConnection(conn =>
            {
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                int count = DapperImp.Count<T>(conn, predicate, Transaction, CommandTimeout);
                var entities = DapperImp.GetPage<T>(conn, predicate, sorts, pageIndex - 1, pageSize, Transaction, CommandTimeout, Buffered);
                return Tuple.Create(entities, count);
            });
            totalCount = results.Item2;
            return results.Item1;
        }
        public IEnumerable<T> GetPage(int pageIndex, int pageSize, out int totalCount, Expression<Func<T, bool>> predicate, object sort)
        {
            totalCount = 0;
            var results = RunInConnection(conn =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                int count = DapperImp.Count<T>(conn, predicateGp, Transaction, CommandTimeout);
                var entities = DapperImp.GetPage<T>(conn, predicateGp, sorts, pageIndex - 1, pageSize, Transaction, CommandTimeout, Buffered);
                return Tuple.Create(entities, count);
            });
            totalCount = results.Item2;
            return results.Item1;
        }
        public IEnumerable<T> GetPage(int pageIndex, int pageSize, out int totalCount, Expression<Func<T, bool>> predicate, IList<ISort> sort)
        {
            totalCount = 0;
            var results = RunInConnection(conn =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                int count = DapperImp.Count<T>(conn, predicateGp, Transaction, CommandTimeout);
                var entities = DapperImp.GetPage<T>(conn, predicateGp, sorts, pageIndex - 1, pageSize, Transaction, CommandTimeout, Buffered);
                return Tuple.Create(entities, count);
            });
            totalCount = results.Item2;
            return results.Item1;
        }
        public IEnumerable<T> GetPage(int pageIndex, int pageSize, out int totalCount, Expression<Func<T, bool>> predicate, SortDirection ascending, params Expression<Func<T, object>>[] sortingExpression)
        {
            totalCount = 0;
            var results = RunInConnection(conn =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sortingExpression.ToSortable(ascending) ?? SortingExtensions.GetDefaultSort<T>();
                int count = DapperImp.Count<T>(conn, predicateGp, Transaction, CommandTimeout);
                var entities = DapperImp.GetPage<T>(conn, predicateGp, sorts, pageIndex - 1, pageSize, Transaction, CommandTimeout, Buffered);
                return Tuple.Create(entities, count);
            });
            totalCount = results.Item2;
            return results.Item1;
        }

        #endregion

        #region 新增
        public dynamic Insert(T entity)
        {
            return RunInConnection(conn =>
            {
                return DapperImp.Insert(conn, entity, Transaction, CommandTimeout);
            });
        }
        public void Insert(IEnumerable<T> entities)
        {
            RunInConnection(conn =>
            {
                DapperImp.Insert(conn, entities, Transaction, CommandTimeout);
            });
        }
        #endregion

        #region 修改
        public bool Update(T entity, bool ignoreAllKeyProperties = false)
        {
            return RunInConnection(conn =>
            {
                return DapperImp.Update(conn, entity, Transaction, CommandTimeout, ignoreAllKeyProperties);
            });
        }

        #endregion

        #region 删除
        public virtual bool Delete(T entity)
        {
            return RunInConnection(conn =>
            {
                return DapperImp.Delete(conn, entity, Transaction, CommandTimeout);
            });
        }
        public virtual bool Delete(object predicate)
        {
            return RunInConnection(conn =>
            {
                return DapperImp.Delete<T>(conn, predicate, Transaction, CommandTimeout);
            });
        }
        public virtual bool Delete(Expression<Func<T, bool>> predicate)
        {
            return RunInConnection(conn =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                return DapperImp.Delete<T>(conn, predicateGp, Transaction, CommandTimeout);
            });
        }

        #endregion

        #region Execute相关
        public int Execute(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return RunInConnection(conn =>
            {
                return conn.Execute(sql, parameters, Transaction, CommandTimeout, commandType);
            });
        }
        public TAny ExecuteScalar<TAny>(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return RunInConnection(conn =>
            {
                return conn.ExecuteScalar<TAny>(sql, parameters, Transaction, CommandTimeout, commandType);
            });
        }

        #endregion

        #region Query相关
        public IEnumerable<T> Query(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return RunInConnection(conn =>
            {
                return conn.Query<T>(sql, parameters, Transaction, Buffered, CommandTimeout, commandType);
            });
        }
        public IEnumerable<TAny> Query<TAny>(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return RunInConnection(conn =>
            {
                return conn.Query<TAny>(sql, parameters, Transaction, Buffered, CommandTimeout, commandType);
            });
        }
        public T QueryFirst(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return RunInConnection(conn =>
            {
                return conn.QueryFirstOrDefault<T>(sql, parameters, Transaction, CommandTimeout, commandType);
            });
        }
        public TAny QueryFirst<TAny>(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return RunInConnection(conn =>
            {
                return conn.QueryFirstOrDefault<TAny>(sql, parameters, Transaction, CommandTimeout, commandType);
            });
        }
        public DataSet QueryDataSet(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return RunInConnection(conn =>
            {
                using (var reader = conn.ExecuteReader(sql, parameters, commandType: commandType))
                {
                    DataSet ds = new DataSet();
                    while (!reader.IsClosed)
                    {
                        ds.Tables.Add().Load(reader);
                    }
                    return ds;
                }
            });
        }

        #endregion

        #region QueryMultiple相关

        public Tuple<IEnumerable<TFirst>, IEnumerable<TSecond>> QueryMultiple<TFirst, TSecond>(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return RunInConnection(conn =>
            {
                using (var gridReader = conn.QueryMultiple(sql, parameters, Transaction, CommandTimeout, commandType))
                {
                    return Tuple.Create(
                        gridReader.Read<TFirst>(),
                        gridReader.Read<TSecond>()
                        );
                }
            });
        }
        public Tuple<IEnumerable<TFirst>, IEnumerable<TSecond>, IEnumerable<TThird>> QueryMultiple<TFirst, TSecond, TThird>(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return RunInConnection(conn =>
            {
                using (var gridReader = conn.QueryMultiple(sql, parameters, Transaction, CommandTimeout, commandType))
                {
                    return Tuple.Create(
                        gridReader.Read<TFirst>(),
                        gridReader.Read<TSecond>(),
                        gridReader.Read<TThird>()
                        );
                }
            });
        }
        public Tuple<IEnumerable<TFirst>, IEnumerable<TSecond>, IEnumerable<TThird>, IEnumerable<TFourth>> QueryMultiple<TFirst, TSecond, TThird, TFourth>(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return RunInConnection(conn =>
            {
                using (var gridReader = conn.QueryMultiple(sql, parameters, Transaction, CommandTimeout, commandType))
                {
                    return Tuple.Create(
                        gridReader.Read<TFirst>(),
                        gridReader.Read<TSecond>(),
                        gridReader.Read<TThird>(),
                        gridReader.Read<TFourth>()
                        );
                }
            });
        }
        public Tuple<IEnumerable<TFirst>, IEnumerable<TSecond>, IEnumerable<TThird>, IEnumerable<TFourth>, IEnumerable<TFifth>> QueryMultiple<TFirst, TSecond, TThird, TFourth, TFifth>(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return RunInConnection(conn =>
            {
                using (var gridReader = conn.QueryMultiple(sql, parameters, Transaction, CommandTimeout, commandType))
                {
                    return Tuple.Create(
                        gridReader.Read<TFirst>(),
                        gridReader.Read<TSecond>(),
                        gridReader.Read<TThird>(),
                        gridReader.Read<TFourth>(),
                        gridReader.Read<TFifth>()
                        );
                }
            });
        }

        #endregion

        #region 异步委托调用
        /// <summary>
        /// 异步委托调用
        /// </summary>
        /// <typeparam name="TAny"></typeparam>
        /// <param name="execSqlFuncAsync"></param>
        /// <returns></returns>
        public async Task<TAny> RunInConnectionAsync<TAny>(Func<IDbConnection, Task<TAny>> execSqlFuncAsync)
        {
            try
            {
                OpenConnection();
                var result = await execSqlFuncAsync(this.Connection);
                return result;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (!this.HasActiveTransaction)
                {
                    this.Close();
                }
            }
        }

        /// <summary>
        /// 异步委托调用
        /// </summary>
        /// <param name="execSqlFuncAsync"></param>
        /// <returns></returns>
        public async Task RunInConnectionAsync(Func<IDbConnection, Task> execSqlFuncAsync)
        {
            try
            {
                OpenConnection();
                await execSqlFuncAsync(this.Connection);
                return;
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (!this.HasActiveTransaction)
                {
                    this.Close();
                }
            }
        }

        #endregion

        #region 异步方法
        public async Task<int> CountAsync(object predicate = null)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                return await DapperImp.CountAsync<T>(conn, predicate, Transaction, CommandTimeout);
            });
        }
        public async Task<int> CountAsync(Expression<Func<T, bool>> predicate)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                return await DapperImp.CountAsync<T>(conn, predicateGp, Transaction, CommandTimeout);
            });
        }
        public async Task<T> GetAsync(dynamic id)
        {
            return await RunInConnectionAsync<T>(async (conn) =>
            {
                return await DapperImp.GetAsync<T>(conn, id, Transaction, CommandTimeout);
            });
        }
        public async Task<T> GetFirstAsync(object predicate = null, object sort = null)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                return (await DapperImp.GetSetAsync<T>(conn, predicate, sorts, 0, 1, Transaction, CommandTimeout)).FirstOrDefault();
            });
        }
        public async Task<T> GetFirstAsync(Expression<Func<T, bool>> predicate)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = SortingExtensions.GetDefaultSort<T>();
                return (await DapperImp.GetSetAsync<T>(conn, predicateGp, sorts, 0, 1, Transaction, CommandTimeout)).FirstOrDefault();
            });
        }
        public async Task<T> GetFirstAsync(Expression<Func<T, bool>> predicate, object sort)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                return (await DapperImp.GetSetAsync<T>(conn, predicateGp, sorts, 0, 1, Transaction, CommandTimeout)).FirstOrDefault();
            });
        }
        public async Task<T> GetFirstAsync(Expression<Func<T, bool>> predicate, IList<ISort> sort)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sort ?? SortingExtensions.GetDefaultSort<T>();
                return (await DapperImp.GetSetAsync<T>(conn, predicateGp, sorts, 0, 1, Transaction, CommandTimeout)).FirstOrDefault();
            });
        }
        public async Task<T> GetFirstAsync(Expression<Func<T, bool>> predicate, SortDirection ascending, params Expression<Func<T, object>>[] sortingExpression)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sortingExpression.ToSortable(ascending) ?? SortingExtensions.GetDefaultSort<T>();
                return (await DapperImp.GetSetAsync<T>(conn, predicateGp, sorts, 0, 1, Transaction, CommandTimeout)).FirstOrDefault();
            });
        }
        public async Task<IEnumerable<T>> GetListAsync(object predicate = null, object sort = null)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var sorts = sort.ToSortable();
                return await DapperImp.GetListAsync<T>(conn, predicate, sorts, Transaction, CommandTimeout);
            });
        }
        public async Task<IEnumerable<T>> GetListAsync(object predicate, IList<ISort> sort)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                return await DapperImp.GetListAsync<T>(conn, predicate, sort, Transaction, CommandTimeout);
            });
        }
        public async Task<IEnumerable<T>> GetListAsync(Expression<Func<T, bool>> predicate, IList<ISort> sort = null)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                return await DapperImp.GetListAsync<T>(conn, predicateGp, sort, Transaction, CommandTimeout);
            });
        }
        public async Task<IEnumerable<T>> GetListAsync(Expression<Func<T, bool>> predicate, SortDirection ascending, params Expression<Func<T, object>>[] sortingExpression)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sortingExpression.ToSortable(ascending);
                return await DapperImp.GetListAsync<T>(conn, predicateGp, sorts, Transaction, CommandTimeout);
            });
        }
        public async Task<IEnumerable<T>> GetSetAsync(int firstResult, int maxResults, object predicate = null, object sort = null)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                return await DapperImp.GetSetAsync<T>(conn, predicate, sorts, firstResult, maxResults, Transaction, CommandTimeout);
            });
        }
        public async Task<IEnumerable<T>> GetSetAsync(int firstResult, int maxResults, object predicate, IList<ISort> sort)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var sorts = sort ?? SortingExtensions.GetDefaultSort<T>();
                return await DapperImp.GetSetAsync<T>(conn, predicate, sorts, firstResult, maxResults, Transaction, CommandTimeout);
            });
        }
        public async Task<IEnumerable<T>> GetSetAsync(int firstResult, int maxResults, Expression<Func<T, bool>> predicate, object sort)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                return await DapperImp.GetSetAsync<T>(conn, predicateGp, sorts, firstResult, maxResults, Transaction, CommandTimeout);
            });
        }
        public async Task<IEnumerable<T>> GetSetAsync(int firstResult, int maxResults, Expression<Func<T, bool>> predicate, IList<ISort> sort)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sort ?? SortingExtensions.GetDefaultSort<T>();
                return await DapperImp.GetSetAsync<T>(conn, predicateGp, sorts, firstResult, maxResults, Transaction, CommandTimeout);
            });
        }
        public async Task<IEnumerable<T>> GetSetAsync(int firstResult, int maxResults, Expression<Func<T, bool>> predicate, SortDirection ascending, params Expression<Func<T, object>>[] sortingExpression)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sortingExpression.ToSortable(ascending) ?? SortingExtensions.GetDefaultSort<T>();
                return await DapperImp.GetSetAsync<T>(conn, predicateGp, sorts, firstResult, maxResults, Transaction, CommandTimeout);
            });
        }
        public async Task<Tuple<IEnumerable<T>, int>> GetPageAsync(int pageIndex, int pageSize, object predicate, object sort)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                int count = await DapperImp.CountAsync<T>(conn, predicate, Transaction, CommandTimeout);
                var entities = await DapperImp.GetPageAsync<T>(conn, predicate, sorts, pageIndex - 1, pageSize, Transaction, CommandTimeout);
                return Tuple.Create(entities, count);
            });
        }
        public async Task<Tuple<IEnumerable<T>, int>> GetPageAsync(int pageIndex, int pageSize, object predicate, IList<ISort> sort)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                int count = await DapperImp.CountAsync<T>(conn, predicate, Transaction, CommandTimeout);
                var entities = await DapperImp.GetPageAsync<T>(conn, predicate, sorts, pageIndex - 1, pageSize, Transaction, CommandTimeout);
                return Tuple.Create(entities, count);
            });
        }
        public async Task<Tuple<IEnumerable<T>, int>> GetPageAsync(int pageIndex, int pageSize, Expression<Func<T, bool>> predicate, object sort)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                int count = await DapperImp.CountAsync<T>(conn, predicate, Transaction, CommandTimeout);
                var entities = await DapperImp.GetPageAsync<T>(conn, predicateGp, sorts, pageIndex - 1, pageSize, Transaction, CommandTimeout);
                return Tuple.Create(entities, count);
            });
        }
        public async Task<Tuple<IEnumerable<T>, int>> GetPageAsync(int pageIndex, int pageSize, Expression<Func<T, bool>> predicate, IList<ISort> sort)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sort.ToSortable() ?? SortingExtensions.GetDefaultSort<T>();
                int count = await DapperImp.CountAsync<T>(conn, predicate, Transaction, CommandTimeout);
                var entities = await DapperImp.GetPageAsync<T>(conn, predicateGp, sorts, pageIndex - 1, pageSize, Transaction, CommandTimeout);
                return Tuple.Create(entities, count);
            });
        }
        public async Task<Tuple<IEnumerable<T>, int>> GetPageAsync(int pageIndex, int pageSize, Expression<Func<T, bool>> predicate, SortDirection ascending, params Expression<Func<T, object>>[] sortingExpression)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                var sorts = sortingExpression.ToSortable(ascending) ?? SortingExtensions.GetDefaultSort<T>();
                int count = await DapperImp.CountAsync<T>(conn, predicate, Transaction, CommandTimeout);
                var entities = await DapperImp.GetPageAsync<T>(conn, predicateGp, sorts, pageIndex - 1, pageSize, Transaction, CommandTimeout);
                return Tuple.Create(entities, count);
            });
        }
        public async Task<dynamic> InsertAsync(T entity)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                return await DapperImp.InsertAsync(conn, entity, Transaction, CommandTimeout);
            });
        }
        public async Task InsertAsync(IEnumerable<T> entities)
        {
            await RunInConnectionAsync(async (conn) =>
            {
                await DapperImp.InsertAsync(conn, entities, Transaction, CommandTimeout);
            });
        }
        public async Task<bool> UpdateAsync(T entity, bool ignoreAllKeyProperties = false)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                return await DapperImp.UpdateAsync(conn, entity, Transaction, CommandTimeout, ignoreAllKeyProperties);
            });
        }
        public async Task<bool> DeleteAsync(T entity)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                return await DapperImp.DeleteAsync(conn, entity, Transaction, CommandTimeout);
            });
        }
        public async Task<bool> DeleteAsync(object predicate)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                return await DapperImp.DeleteAsync<T>(conn, predicate, Transaction, CommandTimeout);
            });
        }
        public async Task<bool> DeleteAsync(Expression<Func<T, bool>> predicate)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                var predicateGp = predicate.ToPredicateGroup();
                return await DapperImp.DeleteAsync<T>(conn, predicateGp, Transaction, CommandTimeout);
            });
        }
        public async Task<int> ExecuteAsync(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                return await conn.ExecuteAsync(sql, parameters, Transaction, CommandTimeout, commandType);
            });
        }
        public async Task<TAny> ExecuteScalarAsync<TAny>(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                return await conn.ExecuteScalarAsync<TAny>(sql, parameters, Transaction, CommandTimeout, commandType);
            });
        }
        public async Task<IEnumerable<T>> QueryAsync(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                return await conn.QueryAsync<T>(sql, parameters, Transaction, CommandTimeout, commandType);
            });
        }
        public async Task<IEnumerable<TAny>> QueryAsync<TAny>(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                return await conn.QueryAsync<TAny>(sql, parameters, Transaction, CommandTimeout, commandType);
            });
        }
        public async Task<T> QueryFirstAsync(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                return await conn.QueryFirstOrDefaultAsync<T>(sql, parameters, Transaction, CommandTimeout, commandType);
            });
        }
        public async Task<TAny> QueryFirstAsync<TAny>(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                return await conn.QueryFirstOrDefaultAsync<TAny>(sql, parameters, Transaction, CommandTimeout, commandType);
            });
        }
        public async Task<DataSet> QueryDataSetAsync(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                using (var reader = await conn.ExecuteReaderAsync(sql, parameters, commandType: commandType))
                {
                    DataSet ds = new DataSet();
                    while (!reader.IsClosed)
                    {
                        ds.Tables.Add().Load(reader);
                    }
                    return ds;
                }
            });
        }
        public async Task<Tuple<IEnumerable<TFirst>, IEnumerable<TSecond>>> QueryMultipleAsync<TFirst, TSecond>(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                using (var gridReader = await conn.QueryMultipleAsync(sql, parameters, Transaction, CommandTimeout, commandType))
                {
                    return Tuple.Create(
                        gridReader.Read<TFirst>(),
                        gridReader.Read<TSecond>()
                        );
                }
            });
        }
        public async Task<Tuple<IEnumerable<TFirst>, IEnumerable<TSecond>, IEnumerable<TThird>>> QueryMultipleAsync<TFirst, TSecond, TThird>(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                using (var gridReader = await conn.QueryMultipleAsync(sql, parameters, Transaction, CommandTimeout, commandType))
                {
                    return Tuple.Create(
                        gridReader.Read<TFirst>(),
                        gridReader.Read<TSecond>(),
                        gridReader.Read<TThird>()
                        );
                }
            });
        }
        public async Task<Tuple<IEnumerable<TFirst>, IEnumerable<TSecond>, IEnumerable<TThird>, IEnumerable<TFourth>>> QueryMultipleAsync<TFirst, TSecond, TThird, TFourth>(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                using (var gridReader = await conn.QueryMultipleAsync(sql, parameters, Transaction, CommandTimeout, commandType))
                {
                    return Tuple.Create(
                        gridReader.Read<TFirst>(),
                        gridReader.Read<TSecond>(),
                        gridReader.Read<TThird>(),
                        gridReader.Read<TFourth>()
                        );
                }
            });
        }
        public async Task<Tuple<IEnumerable<TFirst>, IEnumerable<TSecond>, IEnumerable<TThird>, IEnumerable<TFourth>, IEnumerable<TFifth>>> QueryMultipleAsync<TFirst, TSecond, TThird, TFourth, TFifth>(string sql, object parameters = null, CommandType commandType = CommandType.Text)
        {
            return await RunInConnectionAsync(async (conn) =>
            {
                using (var gridReader = await conn.QueryMultipleAsync(sql, parameters, Transaction, CommandTimeout, commandType))
                {
                    return Tuple.Create(
                        gridReader.Read<TFirst>(),
                        gridReader.Read<TSecond>(),
                        gridReader.Read<TThird>(),
                        gridReader.Read<TFourth>(),
                        gridReader.Read<TFifth>()
                        );
                }
            });
        }
        #endregion
    }
}
